import xlrd

# 读取.xls文件
data = xlrd.open_workbook("用户数据.xls")
# 根据index或sheetName加载sheet
sheetData = data.sheet_loaded(0)
print("根据index或sheetName加载sheet：", sheetData)

# 获取sheet
sheets = data.sheets()
print("获取所有的sheet：", sheets)
print(sheets[0])
sheet_by_index = data.sheet_by_index(0)
print("根据index获取sheet：", sheet_by_index)
sheet_by_name = data.sheet_by_name("Sheet1")
print("根据sheetName获取sheet：", sheet_by_name)
sheet_names = data.sheet_names()
print("获取所有的sheetName的集合：", sheet_names)
sheet_count = data.nsheets
print("获取sheet数量：", sheet_count)

sheet = data.sheet_by_index(0)
# 操作行
rows = sheet.nrows
print("有效行数：", rows)
row = sheet.row(1)
print("获取指定行：", row)
row_types = sheet.row_types(1)
print("获取单元格数据类型（0是空，1是字符串，2是数字，3是日期）：", row_types)
row_cell = sheet.row(1)[2]
print("获取单元格对象：", row_cell)
print("获取单元格值：", row_cell.value)
row_values = sheet.row_values(1)
print("获取某一行的值集合：", row_values)
row_len = sheet.row_len(1)
print("行的长度是：", row_len)

# 操作列
col_count = sheet.ncols
print("列数：", col_count)
col = sheet.col(1)
print("获取第一列对象：", col)
col = sheet.col(1)[1]
print("获取第一列第二个对象：", col)
print("获取第一列第二个对象值：", col.value)
col_values = sheet.col_values(1)
print("第二列所有的值：", col_values)
col_types = sheet.col_types(1)
print("第二列所有的值的类型：", col_types)

# 操作单元格
cell = sheet.cell(1, 2)
print("单元格对象是：", cell)
print("单元格对象值是：", cell.value)
print("单元格类型是：", cell.ctype)
cell_value = sheet.cell_value(1, 2)
print("单元格对象值是：", cell_value)
cell_type = sheet.cell_type(1, 2)
print("单元格类型是：", cell_type)

# 读取Excel
sheet = data.sheet_by_index(0)
rows = sheet.nrows
userList = []
class User:
    pass

for i in range(rows):
    if i > 0:
        user = User()
        user.id = sheet.cell_value(i, 0)
        user.name = sheet.cell_value(i, 1)
        user.age = sheet.cell_value(i, 2)
        # user.birth = sheet.cell_value(i, 3)
        user.birth = xlrd.xldate.xldate_as_datetime(sheet.cell(i, 3).value, 0).strftime("%Y-%m-%d %H:%M:%S") # 读取Excel时间，并处理成指定格式的字符串
        userList.append(user)

print(userList)

import pymysql

con = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    database='runoob_db'
)
cursor = con.cursor()
sql = "insert into user(name, age, birth) values(%s, %s, %s)" # mysql的日期格式需是datetime
vals = [] # 空列表来存储元组
for item in userList:
    vals.append((item.name, item.age, item.birth))

print(vals)
cursor.executemany(sql, vals)
con.commit()
cursor.close()
con.close()
# 取消加载
sheetData = data.unload_sheet(0)
print("根据index或sheetName加载sheet：", sheetData)
